﻿using System;
using System.Text;
using System.Collections.Generic;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using EffiProz.Core.Results;
using EffiProz.Core;
using EffiProz.Core.Lib;
using EffiProz.Core.Navigators;

namespace EffiProz.Test
{
    /// <summary>
    /// Summary description for BasicTableTest
    /// </summary>
    [TestClass]
    public class BasicTableTest : BaseTest
    {
        public BasicTableTest()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion

        [TestMethod]
        public void TestCreateTable()
        {         
            Result resultIn;
            Result resultOut = Result.newExecuteDirectRequest();

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE CACHED TABLE " + table + " (col1 int, col2 double, \"col7\" char(60),col4 varchar(32));";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());

            sql = "SELECT * FROM " + table + ";";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsTrue(resultIn.isData());

            sql = "DROP TABLE " + table + ";";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());

            sql = "SELECT * FROM " + table + ";";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsTrue(resultIn.isError());

        }

        [TestMethod]
        public void TestTableInsert()
        {

            Result resultIn;
            Result resultOut = Result.newExecuteDirectRequest();

            string table = "tablei2";
            string sql = "CREATE TABLE " + table + " (col1 int, col2 varchar(32));";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());

            int col1val = 111;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col1, col2)  VALUES(" + col1val + ",'" + col2val + "');";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());
            Assert.AreEqual(resultIn.getUpdateCount(), 1);

            sql = "SELECT * FROM " + table + ";";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsTrue(resultIn.isData());
            
            Assert.AreEqual(resultIn.getNavigator().size, 1);

            RowSetNavigator it = resultIn.getNavigator();
            object[] row = it.getNext();

            Assert.AreEqual(row[0], col1val);
            Assert.AreEqual(row[1], col2val);
        }

        [TestMethod]
        public void TestTableMultiInsert()
        {

            Result resultIn;
            Result resultOut = Result.newExecuteDirectRequest();

            string table = "tableim2";
            string sql = "CREATE TABLE " + table + " (col1 int, col2 varchar(32));";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());

            int[] col1val = new int[5];
            string[] col2val = new string[5];
            sql = "";
            for (int i = 0; i < col1val.Length; i++)
            {
                col1val[i] = 1002 + i;
                col2val[i] = "Hello" + i;
                sql += " INSERT INTO " + table + " (col1, col2)  VALUES(" + col1val[i] + ",'" + col2val[i] + "'); ";
            }

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());
            Assert.IsTrue(resultIn.isUpdateCount());
            // Assert.AreEqual(col1val.Length, resultIn.getUpdateCount());

            sql = "SELECT * FROM " + table + ";";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsTrue(resultIn.isData());
            Assert.IsFalse(resultIn.isUpdateCount());
            Assert.AreEqual(col1val.Length, resultIn.getNavigator().size);

            RowSetNavigator it = resultIn.getNavigator();
            for (int j = 0; j < col1val.Length; j++)
            {
                object[] row = (object[])it.getNext();
                Assert.AreEqual(col1val[j], row[0]);
                Assert.AreEqual(col2val[j], row[1]);
            }
        }

        [TestMethod]
        public void TestPrimaryKey()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int PRIMARY KEY, col2 varchar(32));";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int col1val = 111;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col1, col2)  VALUES(" + col1val + ",'" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            col1val = 112;
            col2val = "Hoox";
            sql = "INSERT INTO " + table + " (col1, col2)  VALUES(" + col1val + ",'" + col2val + "');"; // none vialation

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            col1val = 111;
            col2val = "Hoof";
            sql = "INSERT INTO " + table + " (col1, col2)  VALUES(" + col1val + ",'" + col2val + "');"; // primary key vialation should fail

            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

        }

        [TestMethod]
        public void TestDelete()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32));";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int col1val = 111;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col1, col2)  VALUES(" + col1val + ",'" + col2val + "');";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            col1val = 1113;
            col2val = "Hoo55";
            sql = "INSERT INTO " + table + " (col1, col2)  VALUES(" + col1val + ",'" + col2val + "');";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            col1val = 6713;
            col2val = "ttHoo55";
            sql = "INSERT INTO " + table + " (col1, col2)  VALUES(" + col1val + ",'" + col2val + "');";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "SELECT * FROM " + table;
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql,3);

            sql = "DELETE FROM " + table;
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "SELECT * FROM " + table;
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 0);

        }


        [TestMethod]
        public void TestDefaultValue()
        {
            //test  default value insert to default value column
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int DEFAULT 1021, col2 varchar(32));";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int col1val = 1021;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col2)  VALUES('" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);


            //test non default value insert to default value column

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);


            table = TestHelper.GetNewTableName();
            sql = "CREATE TABLE " + table + " (col1 int DEFAULT 1021, col2 varchar(32));";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            col1val = 500;
            col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col1, col2)  VALUES(" + col1val + ", '" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);

        }

        [TestMethod]
        public void TestDefaultAutoGen()
        {
            //test  default auto gen with no options
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int GENERATED BY DEFAULT AS IDENTITY, col2 varchar(32));";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int col1val = 0;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col2)  VALUES('" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);


            col1val = 1;
            col2val = "Hoo2";
            sql = "INSERT INTO " + table + " (col2)  VALUES('" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);

            TestHelper.MatchSingleResultRow(resultIn, 1, col1val, col2val);

        }

        [TestMethod]
        public void TestDefaultAutoGenWithStartValue()
        {
            //test  default auto gen with no options
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int GENERATED BY DEFAULT AS IDENTITY (START WITH 113), col2 varchar(32));";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int col1val = 113;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col2)  VALUES('" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);


            col1val = 114;
            col2val = "Hoor";
            sql = "INSERT INTO " + table + " (col2)  VALUES('" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);

            TestHelper.MatchSingleResultRow(resultIn, 1, col1val, col2val);

        }

        [TestMethod]
        public void TestDefaultAutoGenWithStartStepValue()
        {
            //test  default auto gen with no options
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int GENERATED BY DEFAULT AS IDENTITY (START WITH 113 INCREMENT BY 3), col2 varchar(32));";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int col1val = 113;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col2)  VALUES('" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);


            col1val = 116;
            col2val = "Hoor";
            sql = "INSERT INTO " + table + " (col2)  VALUES('" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);

            TestHelper.MatchSingleResultRow(resultIn, 1, col1val, col2val);
        }


        [TestMethod]
        public void TestNull()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32));"; // allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int? col1val = null;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col2)  VALUES('" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);

        }

        [TestMethod]
        public void TestExplicitNull()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int, col2 varchar(32));"; // allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int? col1val = null;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col2)  VALUES('" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);

        }

        [TestMethod]
        public void TestNotNull()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int NOT NULL, col2 varchar(32));"; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int col1val = 55;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col2)  VALUES('" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

            col1val = 55;
            col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col1 , col2)  VALUES(" + col1val + " ,'" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);

        }

        [TestMethod]
        public void TestIdentityColumn()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int IDENTITY, col2 varchar(32));"; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int col1val = 0;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col2)  VALUES('" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);

            col1val = 55;
            col2val = "Hoo2";
            sql = "INSERT INTO " + table + " (col1 , col2)  VALUES(" + col1val + " ,'" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);



            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);

            TestHelper.MatchSingleResultRow(resultIn, 1, col1val, col2val);

        }

        [TestMethod]
        public void TestExplicitNullValue()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int IDENTITY, col2 varchar(32));"; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int col1val = 0;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col1 , col2)  VALUES(NULL, '" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);

        }




        [TestMethod]
        public void TestInsertWithSelect()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1) );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            string table2 = TestHelper.GetNewTableName();
            string sql2 = "CREATE TABLE " + table2 + " (col1 int ,  col2 int );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql2);

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int rcol1val = 3;
            string rcol2val = "test1";
            int rcol3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, rcol1val, rcol2val, rcol3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            sql = "INSERT INTO " + table2 + " SELECT col1,col3 FROM " + table + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table2, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col3val);
            TestHelper.MatchSingleResultRow(resultIn, 1, rcol1val, rcol3val);

        }


        [TestMethod]
        public void TestDropTable()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1) );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int rcol1val = 3;
            string rcol2val = "test1";
            int rcol3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, rcol1val, rcol2val, rcol3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            string table2 = TestHelper.GetNewTableName();
            string sql2 = "CREATE TABLE " + table2 + " (col1 int ,  col2 int );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql2);

            //drop none existing table
            sql = "DROP TABLE dummy;";
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

            sql = "DROP TABLE dummy IF EXISTS;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            //drop existing table
            sql = "DROP TABLE " + table2 + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table2, 2);
            TestHelper.ExecuteSelectSQL_Failure(sessionProxy, sql);

            //drop table with data

            sql = "DROP TABLE " + table + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            TestHelper.ExecuteSelectSQL_Failure(sessionProxy, sql);

        }

        [TestMethod]
        public void TestIdentityFunction()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int IDENTITY, col2 varchar(32)); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int col1val = 0;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col1 , col2)  VALUES(NULL, '" + col2val + "'); CALL IDENTITY();";

            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            object[] objs = (object[])resultIn.getNavigator().getNext();
            col1val = Convert.ToInt32(objs[0]);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);

        }



        [TestMethod]
        public void AlterTableRenameColumn()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32)); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "Hoo";
            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            sql = "ALTER TABLE " + table + " ALTER COLUMN col1 RENAME TO colx;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            TestHelper.ExecuteSelectSQL_Failure(sessionProxy, sql);

            sql = "SELECT colx, col2 FROM " + table + " ;";
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResult(resultIn, col1val, col2val);
        }

        [TestMethod]
        public void AlterTableSetDefaultColumn()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32)); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "Hoo";
            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            sql = "ALTER TABLE " + table + " ALTER COLUMN col2 SET DEFAULT 'TestDef';";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            sql = TestHelper.CreateStdInsert(table, 1, col1val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 1, col1val, "TestDef");
        }

        [TestMethod]
        public void AlterTableSetNullColumn()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32)); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "Hoo";
            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            sql = "ALTER TABLE " + table + " ALTER COLUMN col2 SET NOT NULL;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdInsert(table, 1, col1val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

        }

        [TestMethod]
        public void AlterTableDropColumn()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32)); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "Hoo";
            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            sql = "ALTER TABLE " + table + " DROP COLUMN col2 ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdInsert(table, 1, col1val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);


        }

        [TestMethod]
        public void AlterTableAddColumn()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32)); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "Hoo";

            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            sql = "ALTER TABLE " + table + " Add COLUMN col3 int ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col3val = 897;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = "SELECT * FROM " + table;
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 1, col1val, col2val, col3val);

        }

        [TestMethod]
        public void AlterTableAddBeforeColumn()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32)); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "Hoo";

            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            sql = "ALTER TABLE " + table + " Add COLUMN col3 int BEFORE col1;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col3val = 897;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = "SELECT * FROM " + table;
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 1, col3val, col1val, col2val);

        }

        [TestMethod]
        public void AlterTableRenameTable()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32)); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "Hoo";

            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            string newTable = TestHelper.GetNewTableName();
            sql = "ALTER TABLE " + table + " RENAME TO " + newTable + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(newTable, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);
        }

        [TestMethod]
        public void AlterTableRestartColumn()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 int IDENTITY); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            int col2val = 0;

            sql = TestHelper.CreateStdInsert(table, 1, col1val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);

            sql = "ALTER TABLE " + table + " ALTER COLUMN col2 RESTART WITH 1000 ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            col2val = 1000;
            sql = TestHelper.CreateStdInsert(table, 1, col1val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 1, col1val, col2val);
        }


        [TestMethod]
        public void AlterTableChangeDefinitionColumn()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 int); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 67;
            int col2val = 5;

            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);

            sql = "ALTER TABLE " + table + " ALTER COLUMN col2 BIGINT ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int rcol1val = 77;
            int rcol2val = 6666;
            sql = TestHelper.CreateStdInsert(table, 2, rcol1val, rcol2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, (long)col2val);
            TestHelper.MatchSingleResultRow(resultIn, 1, rcol1val, (long)rcol2val);
        }

        [TestMethod]
        public void SetReadOnly()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 int); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 67;
            int col2val = 5;

            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);

            sql = "SET READONLY TRUE; ";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int rcol1val = 77;
            int rcol2val = 6666;
            sql = TestHelper.CreateStdInsert(table, 2, rcol1val, rcol2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);

            sql = "SET READONLY FALSE; ";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            sql = TestHelper.CreateStdInsert(table, 2, rcol1val, rcol2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);
            TestHelper.MatchSingleResultRow(resultIn, 1, rcol1val, rcol2val);

        }


        [TestMethod]
        public void SetIgnoreCase()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32)); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 67;
            string col2val = "IranTHa";

            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "SELECT col1, col2 FROM " + table + " WHERE col2='" + col2val + "' ;";
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);

            //all lower case select

            sql = "SELECT col1, col2 FROM " + table + " WHERE col2='" + col2val.ToLower() + "' ;";
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 0);

            sql = "SET IGNORECASE TRUE; ";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            string table2 = TestHelper.GetNewTableName();
            sql = "CREATE TABLE " + table2 + " (col1 int , col2 varchar(32)); "; // not allow null for col1
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdInsert(table2, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "SELECT col1, col2 FROM " + table2 + " WHERE col2='" + col2val.ToLower() + "' ;"; // success
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);

            sql = "SELECT col1, col2 FROM " + table2 + " WHERE col2='" + col2val.ToUpper() + "' ;"; // success
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);

            // Test Ignore case false

            sql = "SET IGNORECASE FALSE; ";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            string table3 = TestHelper.GetNewTableName();
            sql = "CREATE TABLE " + table3 + " (col1 int , col2 varchar(32)); "; // not allow null for col1
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdInsert(table3, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "SELECT col1, col2 FROM " + table3 + " WHERE col2='" + col2val + "' ;"; // success
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);

            sql = "SELECT col1, col2 FROM " + table3 + " WHERE col2='" + col2val.ToUpper() + "' ;"; // failure
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 0);

        }

        [TestMethod]
        public void ReadOnlyTables()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 int); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 67;
            int col2val = 5;

            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);

            sql = "SET TABLE " + table + " READONLY TRUE; ";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int rcol1val = 77;
            int rcol2val = 6666;
            sql = TestHelper.CreateStdInsert(table, 2, rcol1val, rcol2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

            sql = "SET TABLE " + table + " READONLY FALSE; ";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdInsert(table, 2, rcol1val, rcol2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);
            TestHelper.MatchSingleResultRow(resultIn, 1, rcol1val, rcol2val);

        }

        [TestMethod]
        public void PreparedStatements()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 int); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);            
            

            sql = "INSERT INTO "+table+"(col1,col2)  VALUES( ? , ?)";
            resultIn = TestHelper.GetPrepareStatement(sessionProxy, sql);

            int col1val = 67;
            int col2val = 5;
            TestHelper.ExecutePrepareStatement(sessionProxy, resultIn, new object[] { col1val, col2val });

            col1val = 100;
            col2val = 105;
            TestHelper.ExecutePrepareStatement(sessionProxy, resultIn, new object[] { col1val, col2val });

         
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);

        }

        [TestMethod]
        public void PreparedStatementsNP()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 int); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            sql = "INSERT INTO " + table + "(col1,col2)  VALUES( @pcol1 ,@pcol2)";
            resultIn = TestHelper.GetPrepareStatement(sessionProxy, sql);

            int col1val = 67;
            int col2val = 5;
            TestHelper.ExecutePrepareStatement(sessionProxy, resultIn, new object[] { col1val, col2val });

            col1val = 100;
            col2val = 105;
            TestHelper.ExecutePrepareStatement(sessionProxy, resultIn, new object[] { col1val, col2val });


            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);

        }     


    }
}
